# coding=utf-8

import sqlite3


# 处理用户数据，新建数据库，数据库的插入、更新与查询
class UserData:

    def __init__(self):
        self.conn = sqlite3.connect("UserData.db", check_same_thread=False)
        self.curs = self.conn.cursor()

    # 连接数据库
    def connect(self):
        self.conn = sqlite3.connect("UserData.db", check_same_thread=False)
        self.curs = self.conn.cursor()

    # 关闭数据库
    def close(self):
        self.conn.close()

    # 新建数据库，只需调用一次
    def create(self):
        # 新建表，包括用户名、密码、在线时长
        self.curs.execute("""
        CREATE TABLE user (
            username    TEXT    PRIMARY KEY,
            password    TEXT,
            online_time FLOAT
        )
        """)
        # 插入内建用户
        self.insert_user("netease1", "123")
        self.insert_user("netease2", "123")
        self.insert_user("netease3", "123")
        self.insert_user("netease4", "123")
        # commit在insert函数内部，close在外部调用

    # 查询用户名是否存在
    def is_existed(self, username):
        query = "SELECT username FROM user WHERE username = ?"
        self.curs.execute(query, [username, ])
        return self.curs.fetchall()

    # 验证用户名和密码
    def verify(self, username, password):
        query = "SELECT password FROM user WHERE username = ?"
        self.curs.execute(query, [username, ])
        result = self.curs.fetchall()
        return password == result[0][0]

    # 获取在线时长
    def get_online_time(self, username):
        query = "SELECT online_time FROM user WHERE username = ?"
        self.curs.execute(query, [username, ])
        result = self.curs.fetchall()
        return result[0][0]

    # 更新在线时长
    def update_online_time(self, username, time):
        query = "UPDATE user SET online_time = online_time + ? WHERE username = ?"
        self.curs.execute(query, [time, username, ])
        self.conn.commit()

    # 设置在线时长，实际没有用到
    def set_online_time(self, username, time):
        query = "UPDATE user SET online_time = ? WHERE username = ?"
        self.curs.execute(query, [time, username, ])
        self.conn.commit()

    # 插入新用户
    def insert_user(self, username, password):
        query = "INSERT INTO user VALUES (?, ?, ?)"
        self.curs.execute(query, [username, password, 0, ])
        self.conn.commit()

# 新建数据库，及测试
if __name__ == "__main__":
    user_data = UserData()
    # 新建数据库
    try:
        user_data.create()
    except sqlite3.OperationalError:
        pass    # 当表存在时（即非第一次调用）会返回异常，忽略
    finally:
        user_data.close()

    # 测试
    print user_data.verify("netease1", "123")
    print user_data.verify("netease1", "1234")
    print user_data.is_existed("netease1")
    print user_data.is_existed("netease5")

    print user_data.get_online_time("netease1")
    user_data.update_online_time("netease1", 10)
    print user_data.get_online_time("netease1")
    user_data.update_online_time("netease1", 10)
    print user_data.get_online_time("netease1")

    user_data.close()
